package cn.com.test.common.mysql;

import cn.com.test.common.dto.UserDTO;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

@WebServlet("/web/queryUser")
public class QueryUserServlet extends HttpServlet {

	public void doGet(HttpServletRequest request, HttpServletResponse response) {
		int curPage, pageSize, totalCount = 0, totalPage;

		try {
			curPage = Integer.parseInt(request.getParameter("pageNum"));
		} catch (Exception e) { //获取页码失败默认第一页
			curPage = 1;
		}
		try {
			pageSize = Integer.parseInt(request.getParameter("pageSize"));
		} catch (Exception e) {
			pageSize = 5;
		}

		Connection con = ConnectionFactory.getConnection();
		String sql = "select u.id,u.user_name,u.register_time,count(1) over() as count from  edaily.users u limit ?,?" +
				" ;";
		try {
			PreparedStatement ps = con.prepareStatement(sql);
			ps.setInt(1, (curPage - 1) * pageSize);
			ps.setInt(2, pageSize);
			ResultSet rs = ps.executeQuery();

			//封装查询结果
			List<UserDTO> users = new ArrayList<UserDTO>();
			while (rs.next()) {
				UserDTO user = new UserDTO();
				user.setId(rs.getInt(1));
				user.setUserName(rs.getString(2));
				user.setRegisterTime(rs.getDate(3));
				if (rs.isFirst()) {
					totalCount = rs.getInt(4);
				}
				users.add(user);
			}
			request.setAttribute("users", users);

			totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : (totalCount / pageSize) + 1;

			//创建分页栏
			StringBuilder pageBar = new StringBuilder();
			for (int i = 1; i <= totalPage; i++) {
				if (i == curPage) {
					pageBar.append("【" + i + "】");
				} else {
					pageBar.append("<a href = '/edaily-web/web/queryUser?pageNum=" + i + "' >" + i + "</a>");
				}
				pageBar.append(" ");
			}
			request.setAttribute("pageBar", pageBar.toString());
			request.getRequestDispatcher("queryUser.jsp").forward(request, response);
			rs.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} catch (ServletException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		} finally {
			try {
				System.out.println("关闭连接");
				con.close();

			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}
